package com.huaqin.excel.handler;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;

import org.omg.CORBA.PUBLIC_MEMBER;

/*import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;*/


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;

import com.huaqin.excel.handler.ExcelDataSource.CHECKLIST_TYPE;
/**
 * 
 * @author guojianhui
 *
 */
public class ExcelReadAndWrite {
	
	   public static String outFile_Erro = "Load Template Erro!";  
	      
	    FileOutputStream  fileOutputStream  =  null;  
	      
	    HSSFWorkbook workbook = null;  
	    HSSFSheet   sheet = null;  
	  
	    HSSFPatriarch  patriarch  =  null;  
	    
	    
	    /**
	     * load xsl data to workbook
	     * @param templateURL
	     * @author guojianhui
	     * 
	     */
	    public  void loadTemplate(String templateURL, int page)  
	    {  
	        // TODO Auto-generated method stub  
	        boolean a = templateURL.trim().indexOf(".xls") == -1;  
	        boolean b = templateURL.trim().indexOf(".XLS") == -1;  
//	      boolean c = templateURL.trim().indexOf(".xlsx") == -1;  
//	      boolean d = templateURL.trim().indexOf(".XLSX") == -1;  
	        if(templateURL == null  || templateURL.trim().equals("") )  
	        {
	            System.out.println("file cannot be null");
	        }  
	        else if(a&&b)// && c&&d)  
	        {
	            System.out.println("file format is error");
	              
	        }  
	        else{  
	            try{  
	            	System.out.println("start load template "+templateURL);
	                FileInputStream templateFile_Input = new FileInputStream(templateURL);            
	                POIFSFileSystem fs = new POIFSFileSystem(templateFile_Input);  
	                  
	                workbook = new  HSSFWorkbook(fs); 

	                sheet =  workbook.getSheetAt(page);  
	                  
//	                System.out.println("========"+templateURL+"file reload alreadly completed======");  
	            }catch(Exception e){  
	                System.err.println(outFile_Erro);  
	            }     
	        }  
	        
	                   
	    }  
	      
	    /**
	     * write cell special
	     * @param templateURL
	     * @author guojianhui
	     * 
	     */
	    public void writeInTemplate( String newContent, int beginRow, int beginCol)  
	    {     
	        HSSFRow  row  = sheet.getRow(beginRow);   
	        if(null == row ){  
	            //judge null, or you will set the module well
	            row = sheet.createRow(beginRow);  
	        }  
	        HSSFCell   cell   = row.getCell(beginCol);  
	        System.out.println("write in to template");
	        if(null == cell) {  
	            cell = row.createCell(beginCol);  
	        }  
	        //setting write file is string
	        cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	        //write data into the cell 
	        
	        cell.setCellValue(newContent);   
//	        System.out.println(newContent);
	        setRedStyle(cell);
	    }
	    
	    /**
	     * save workbook
	     * @param templateURL
	     * @author guojianhui
	     * 
	     */
	    public void SaveTemplate(String templateFile)  
	    {  
	        try{  
	              
	            //create out put stream
	            fileOutputStream = new FileOutputStream(templateFile);  
	            workbook.write(fileOutputStream);  
	            System.out.println("save template");
	          
	        }catch(Exception e){  
	            e.printStackTrace();  
//	            System.out.println("IO Erro" + e.getMessage());  
	        }finally  
	        {  
	            if(fileOutputStream != null)  
	            {  
	                try{  
	                    fileOutputStream.close();  
	                }catch(IOException io){  
	                    io.printStackTrace();  
	                }  
	            }  
	        }  
	    } 
	    
	    /**
	     * cell set background 
	     * @param cell
	     */
	    public void setRedStyle(Cell cell){
	        if(cell!=null){
	            CellStyle newstyle = cell.getSheet().getWorkbook().createCellStyle();
	            CellStyle style = cell.getCellStyle();
	            if(style!=null){
	                newstyle.cloneStyleFrom(style);
	            }
	            newstyle.setFillForegroundColor(IndexedColors.RED.getIndex());
	            newstyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
	            cell.setCellStyle(newstyle);
	        }
	    }
		
	    public String  readInCell( int beginRow, int beginCol)  
	    {     
	    	String s = null ;
	        HSSFRow  row  = sheet.getRow(beginRow);   
	        if(null == row ){  
	        	//judge null, or you will set the module well  
	            row = sheet.createRow(beginRow);  
	        }  
	        HSSFCell   cell   = row.getCell(beginCol);  
	        if(null == cell){  
	            cell = row.createCell(beginCol);  
	        }  
	       //write data into the cell 
	       s =  cell.getStringCellValue();   
//	       System.out.println("========"+s+"files have readed======");  
	       return s ; 
	    }
}
